/*
 Navicat Premium Data Transfer

 Source Server         : testsqlserver
 Source Server Type    : SQL Server
 Source Server Version : 11002100
 Source Host           : localhost:1433
 Source Catalog        : jdbc
 Source Schema         : dbo

 Target Server Type    : SQL Server
 Target Server Version : 11002100
 File Encoding         : 65001

 Date: 19/07/2018 15:30:50
*/


-- ----------------------------
-- Table structure for sysdiagrams
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[sysdiagrams]') AND type IN ('U'))
	DROP TABLE [dbo].[sysdiagrams]
GO

CREATE TABLE [dbo].[sysdiagrams] (
  [name] sysname  NOT NULL,
  [principal_id] int  NOT NULL,
  [diagram_id] int  IDENTITY(1,1) NOT NULL,
  [version] int  NULL,
  [definition] varbinary(max)  NULL
)
GO

ALTER TABLE [dbo].[sysdiagrams] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Records of sysdiagrams
-- ----------------------------
SET IDENTITY_INSERT [dbo].[sysdiagrams] ON
GO

INSERT INTO [dbo].[sysdiagrams] ([name], [principal_id], [diagram_id], [version], [definition]) VALUES (N'Diagram_0', N'1', N'1', N'1', 0xD0CF11E0A1B11AE1000000000000000000000000000000003E000300FEFF0900060000000000000000000000010000000100000000000000001000000200000001000000FEFFFFFF0000000000000000FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFDFFFFFF0F000000FEFFFFFF040000000E000000060000000700000008000000090000000A0000000B0000000C0000000D000000FEFFFFFF10000000FEFFFFFF11000000120000001300000014000000150000001600000017000000FEFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF52006F006F007400200045006E00740072007900000000000000000000000000000000000000000000000000000000000000000000000000000000000000000016000500FFFFFFFFFFFFFFFF0200000000000000000000000000000000000000000000000000000000000000E0889638A81CD40103000000C0150000000000006600000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000004000201FFFFFFFFFFFFFFFFFFFFFFFF0000000000000000000000000000000000000000000000000000000000000000000000000000000092030000000000006F000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000040002010100000004000000FFFFFFFF00000000000000000000000000000000000000000000000000000000000000000000000005000000EE10000000000000010043006F006D0070004F0062006A0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000012000201FFFFFFFFFFFFFFFFFFFFFFFF0000000000000000000000000000000000000000000000000000000000000000000000000F0000005F000000000000000100000002000000030000000400000005000000060000000700000008000000090000000A0000000B0000000C0000000D0000000E000000FEFFFFFF10000000FEFFFFFF12000000130000001400000015000000160000001700000018000000190000001A0000001B0000001C0000001D0000001E0000001F000000200000002100000022000000230000002400000025000000260000002700000028000000290000002A0000002B0000002C0000002D0000002E0000002F000000300000003100000032000000330000003400000035000000360000003700000038000000390000003A0000003B0000003C0000003D0000003E0000003F000000400000004100000042000000430000004400000045000000460000004700000048000000FEFFFFFFFEFFFFFF4B0000004C0000004D0000004E0000004F000000500000005100000052000000530000005400000055000000FEFFFFFFFEFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF000430000A1E100C050000800A0000000F00FFFF0A000000007D000009700000E03C00000B8400000E73000078ECFFFF2A0B0000DE805B10F195D011B0A000AA00BDCB5C00001800B300000006000080010000408622BC0253797374656D00C00300000038002B00000009000000D9E6B0E91C81D011AD5100A0C90F5739F43B7F847F61C74385352986E1D552F8A0327DB2D86295428D98273C25A2DA2D00002C0043200000000000000000000053444DD2011FD1118E63006097D2DF4834C9D2777977D811907000065B840D9C00002C0043200000000000000000000051444DD2011FD1118E63006097D2DF4834C9D2777977D811907000065B840D9C0A0000008C020000008A011300002C00A50900000700008001000000980200000080000003000080536368477269640028230000000000006275734700002C00A50900000700008002000000980200000080000003000080536368477269640000000000000000006361724700003000A50900000700008003000000A002000000800000070000805363684772696400D0200000781E0000745F61706172740000003000A509000007000080040000009E0200000080000006000080536368477269640000000000A41F0000745F646F726D640000009800A5090000070000800500000052000000018000006E000000436F6E74726F6C0015160000AB2500001C2074005F00610070006100720074001D200E4E1C2074005F0064006F0072006D001D204B4EF49584767351FB7C1C2046004B005F005F0074005F0064006F0072006D005F005F00690064005F00610070006100720074005F005F00350032003500390033004300420038001D20000000002800B50100000700008006000000310000006D00000002800000436F6E74726F6C00A51200005525000000003000A509000007000080070000009C020000008000000500008053636847726964000000000040380000745F73747569640000006400A5090000070000800800000052000000018000003C000000436F6E74726F6C00210A0000EA2B00001C2074005F0064006F0072006D001D200E4E1C2074005F007300740075001D204B4EF49584767351FB7C1C20690064005F0064006F0072006D001D2000002800B50100000700008009000000310000003F00000002800000436F6E74726F6C004F060000A532000000002C00A5090000070000800A0000009402000000800000010000805363684772696400BA4500000000000075636847000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100FEFF030A0000FFFFFFFF00000000000000000000000000000000170000004D6963726F736F66742044445320466F726D20322E300010000000456D626564214334120800000041170000FD0E0000785634120700000014010000620075007300000038350E1358CE4B130700000068FD040D0000000068FD040D38CD4B130400000000000000030000000000000000000000000100000001000000000000F07D2B68BC3E386874FE7C686043376890433768A0433768B48BE11104373968BCE63768DCE63768F07D2B68BC3E386874FE7C6800D0376800D037680CD037680CD0376818D0376818D0376824D0376824D0376830D0376830D037683CD037683CD0376848D0376848D0376854D0376854D0376860D0376860D037686CD037686CD0376878D0376878D0376884D0376884D0376890D0376890D037689CD037689CD03768A8D03768A8D0000000000000000000000100000005000000540000002C0000002C0000002C000000340000000000000000000000A7290000E0170000000000002D010000070000000C000000070000001C010000F708000053070000390300000B040000D0020000DD04000018060000A203000018060000BC07000046050000000000000100000041170000FD0E000000000000040000000400000002000000020000001C010000E60A00000000000001000000F21300008306000000000000010000000100000002000000020000001C010000F70800000100000000000000F21300000804000000000000000000000000000002000000020000001C010000F7080000000000000000000055320000C226000000000000000000000D00000004000000040000001C010000F70800009B0A00008106000078563412040000005000000001000000010000000B000000000000000100000002000000030000000400000005000000060000000700000008000000090000000A00000004000000640062006F000000040000006200750073000000214334120800000041170000FD0E00007856341207000000140100006300610072000000A8DD0013E05E1F130700000080741A130000000080741A13105F1F130400000000000000030000000000000000000000000100000001000000000000F07D2B68BC3E386874FE7C68DCE63768C4EBE11104373968BCE63768DCE63768F07D2B68F47C2B6800000000000000000700000000000000000000000700000000000000010000000600000000000000000000000700000000000000000000000700000000000000000000000700000000000000000000000700000000000000000000000300000000000000000000000300000000000000010000000700000001000000010000000700000000000000000000000100000005000000540000002C0000002C0000002C000000340000000000000000000000A7290000E0170000000000002D010000070000000C000000070000001C010000F708000053070000390300000B040000D0020000DD04000018060000A203000018060000BC07000046050000000000000100000041170000FD0E000000000000040000000400000002000000020000001C010000E60A00000000000001000000F21300008306000000000000010000000100000002000000020000001C010000F70800000100000000000000F21300000804000000000000000000000000000002000000020000001C010000F7080000000000000000000055320000C226000000000000000000000D00000004000000040000001C010000F70800009B0A00008106000078563412040000005000000001000000010000000B000000000000000100000002000000030000000400000005000000060000000700000008000000090000000A00000004000000640062006F0000000400000063006100720000002143341208000000411700005E11000078563412070000001401000074005F006100700061007200740000000800000080741A130000000080741A13505F1F130500000000000000030000000000000000000000000100000001000000000000F07D2B68BC3E386874FE7C68DCE63768DC273768E8273768F07D2B68F47C2B68DC273768E8273768F07D2B68F47C2B680700000000000000000000000700000000000000010000000600000000000000000000000700000000000000000000000700000000000000000000000700000000000000000000000700000000000000000000000300000000000000000000000300000000000000010000000700000001000000010000000700000000000000000000000100000005000000540000002C0000002C0000002C000000340000000000000000000000A7290000E0170000000000002D010000070000000C000000070000001C010000F708000053070000390300000B040000D0020000DD04000018060000A203000018060000BC070000460500000000000001000000411700005E11000000000000050000000500000002000000020000001C010000E60A00000000000001000000F21300008306000000000000010000000100000002000000020000001C010000F70800000100000000000000F21300000804000000000000000000000000000002000000020000001C010000F7080000000000000000000055320000C226000000000000000000000D00000004000000040000001C010000F70800009B0A00008106000078563412040000005800000001000000010000000B000000000000000100000002000000030000000400000005000000060000000700000008000000090000000A00000004000000640062006F0000000800000074005F00610070006100720074000000214334120800000041170000FD0E000078563412070000001401000074005F0064006F0072006D000000491310000000787133130000000078713313705E1F130800000000000000080000000000000000000000000100000001000000000000A07C2B68F07D2B68387D2B68DCE63768DC273768E8273768F07D2B68F47C2B68DC273768E8273768F07D2B68F47C2B680700000000000000000000000700000000000000010000000600000000000000000000000700000000000000000000000700000000000000000000000700000000000000000000000700000000000000000000000300000000000000000000000300000000000000010000000700000001000000010000000700000000000000000000000100000005000000540000002C0000002C0000002C000000340000000000000000000000A7290000E0170000000000002D010000070000000C000000070000001C010000F708000053070000390300000B040000D0020000DD04000018060000A203000018060000BC07000046050000000000000100000041170000FD0E000000000000040000000400000002000000020000001C010000E60A00000000000001000000F2130000FE08000000000000020000000200000002000000020000001C010000F70800000100000000000000F21300000804000000000000000000000000000002000000020000001C010000F7080000000000000000000055320000C226000000000000000000000D00000004000000040000001C010000F70800009B0A00008106000078563412040000005600000001000000010000000B000000000000000100000002000000030000000400000005000000060000000700000008000000090000000A00000004000000640062006F0000000700000074005F0064006F0072006D00000002000B00D02000004227000041170000422700000000000002000000F0F0F00000000000000000000000000000000000010000000600000000000000A512000055250000471400003E01000039000000010000020000471400003E010000020000000000FFFFFF0008000080010000001500018600009001905F010004CBCECCE51E0046004B005F005F0074005F0064006F0072006D005F005F00690064005F00610070006100720074005F005F0035003200350039003300430042003800214334120800000041170000BE13000078563412070000001401000074005F007300740075000000000000000000000000000000000000000000000000000000000000000000000000000000B484D26AB484D26ABC84D26ABC84D26AC884D26AC884D26AD484D26AD484D26AE084D26AE084D26A5847D16A5847D16A6447D16A6447D16A7047D16A7047D16AEC84D26AEC84D26A7C47D16A7C47D16AF884D26AF884D26A0485D26A0485D26A1085D26A1085D26A8847D16A8847D16A1C85D26A1C85D26A2885D26A2885D26A3485D26A3485D26A9447D16A9447D16AA047D16AA047D16AAC47D16AAC47D16A4085D26A4085D26A4C85D26A4C85D26AB847D16AB847D16AC447D16AC447000000000000000000000100000005000000540000002C0000002C0000002C000000340000000000000000000000A72900005B1A0000000000002D010000080000000C000000070000001C010000F708000053070000390300000B040000D0020000DD04000018060000A203000018060000BC07000046050000000000000100000041170000BE13000000000000060000000600000002000000020000001C010000E60A00000000000001000000F2130000790B000000000000030000000300000002000000020000001C010000F70800000100000000000000F21300000804000000000000000000000000000002000000020000001C010000F7080000000000000000000055320000C226000000000000000000000D00000004000000040000001C010000F70800009B0A00008106000078563412040000005400000001000000010000000B000000000000000100000002000000030000000400000005000000060000000700000008000000090000000A00000004000000640062006F0000000600000074005F00730074007500000002000B00B80B0000A12E0000B80B0000403800000000000002000000F0F0F000000000000000000000000000000000000100000009000000000000004F060000A5320000BA0400003E01000030000000010000020000BA0400003E010000020000000000FFFFFF0008000080010000001500018600009001905F010004CBCECCE50700690064005F0064006F0072006D002143341208000000411700009C0C0000785634120700000014010000750000005C9C7C680300000000000000E0742B68E0742B681CB138681CB13868A8B13868A8B138681C752B681C752B68D88E2B68D88E2B6888B1386888B13868B8B13868B8B1386860B1386860B1386880B1386880B13868608F2B68608F2B6814B1386814B13868B0B13868B0B13868E4B03868E4B03868F0B03868F0B03868FCB03868FCB0386808B1386808B1386824B1386824B1386830B1386830B138683CB138683CB1386848B1386848B1386854B1386854B1386868B1386868B1386874B1386874B1386890B1386890B138689CB138689CB13868C0B13868C0B13868CCB13868CCB13868D8B13868D8B1000000000000000000000100000005000000540000002C0000002C0000002C000000340000000000000000000000A7290000E0170000000000002D010000070000000C000000070000001C010000F708000053070000390300000B040000D0020000DD04000018060000A203000018060000BC070000460500000000000001000000411700009C0C000000000000030000000300000002000000020000001C010000E60A00000000000001000000F21300008306000000000000010000000100000002000000020000001C010000F70800000100000000000000F21300000804000000000000000000000000000002000000020000001C010000F7080000000000000000000055320000C226000000000000000000000D00000004000000040000001C010000F70800009B0A00008106000078563412040000004C00000001000000010000000B000000000000000100000002000000030000000400000005000000060000000700000008000000090000000A00000004000000640062006F000000020000007500000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000646564204F626A6563740000000000F439B2710000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000C00000078ECFFFF2A0B00000100260000007300630068005F006C006100620065006C0073005F00760069007300690062006C0065000000010000000B0000001E000000000000000000000000000000000000006400000000000000000000000000000000000000000000000000010000000100000000000000000000000000000000000000D00200000600280000004100630074006900760065005400610062006C00650056006900650077004D006F006400650000000100000008000400000031000000200000005400610062006C00650056006900650077004D006F00640065003A00300000000100000008003A00000034002C0030002C003200380034002C0030002C0032003200390035002C0031002C0031003800370035002C0035002C0031003200340035000000200000005400610062006C00650056006900650077004D006F00640065003A00310000000100000008001E00000032002C0030002C003200380034002C0030002C0032003700390030000000200000005400610062006C00650056006900650077004D006F00640065003A00320000000100000008001E00000032002C0030002C003200380034002C0030002C00320032000300440064007300530074007200650061006D000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000160002000300000006000000FFFFFFFF00000000000000000000000000000000000000000000000000000000000000000000000011000000F20D00000000000053006300680065006D00610020005500440056002000440065006600610075006C0074000000000000000000000000000000000000000000000000000000000026000200FFFFFFFFFFFFFFFFFFFFFFFF000000000000000000000000000000000000000000000000000000000000000000000000490000001600000000000000440053005200450046002D0053004300480045004D0041002D0043004F004E00540045004E0054005300000000000000000000000000000000000000000000002C0002010500000007000000FFFFFFFF0000000000000000000000000000000000000000000000000000000000000000000000004A000000F40200000000000053006300680065006D00610020005500440056002000440065006600610075006C007400200050006F007300740020005600360000000000000000000000000036000200FFFFFFFFFFFFFFFFFFFFFFFF000000000000000000000000000000000000000000000000000000000000000000000000560000001200000000000000390035000000200000005400610062006C00650056006900650077004D006F00640065003A00330000000100000008001E00000032002C0030002C003200380034002C0030002C0032003200390035000000200000005400610062006C00650056006900650077004D006F00640065003A00340000000100000008003E00000034002C0030002C003200380034002C0030002C0032003200390035002C00310032002C0032003700310035002C00310031002C0031003600360035000000020000000200000000000000000000000000000000000000D00200000600280000004100630074006900760065005400610062006C00650056006900650077004D006F006400650000000100000008000400000031000000200000005400610062006C00650056006900650077004D006F00640065003A00300000000100000008003A00000034002C0030002C003200380034002C0030002C0032003200390035002C0031002C0031003800370035002C0035002C0031003200340035000000200000005400610062006C00650056006900650077004D006F00640065003A00310000000100000008001E00000032002C0030002C003200380034002C0030002C0032003700390030000000200000005400610062006C00650056006900650077004D006F00640065003A00320000000100000008001E00000032002C0030002C003200380034002C0030002C0032003200390035000000200000005400610062006C00650056006900650077004D006F00640065003A00330000000100000008001E00000032002C0030002C003200380034002C0030002C0032003200390035000000200000005400610062006C00650056006900650077004D006F00640065003A00340000000100000008003E00000034002C0030002C003200380034002C0030002C0032003200390035002C00310032002C0032003700310035002C00310031002C0031003600360035000000030000000300000000000000000000000000000000000000D00200000600280000004100630074006900760065005400610062006C00650056006900650077004D006F006400650000000100000008000400000031000000200000005400610062006C00650056006900650077004D006F00640065003A00300000000100000008003A00000034002C0030002C003200380034002C0030002C0032003200390035002C0031002C0031003800370035002C0035002C0031003200340035000000200000005400610062006C00650056006900650077004D006F00640065003A00310000000100000008001E00000032002C0030002C003200380034002C0030002C0032003700390030000000200000005400610062006C00650056006900650077004D006F00640065003A00320000000100000008001E00000032002C0030002C003200380034002C0030002C0032003200390035000000200000005400610062006C00650056006900650077004D006F00640065003A00330000000100000008001E00000032002C0030002C003200380034002C0030002C0032003200390035000000200000005400610062006C00650056006900650077004D006F00640065003A00340000000100000008003E00000034002C0030002C003200380034002C0030002C0032003200390035002C00310032002C0032003700310035002C00310031002C0031003600360035000000040000000400000000000000000000000000000000000000D00200000600280000004100630074006900760065005400610062006C00650056006900650077004D006F006400650000000100000008000400000031000000200000005400610062006C00650056006900650077004D006F00640065003A00300000000100000008003A00000034002C0030002C003200380034002C0030002C0032003200390035002C0031002C0031003800370035002C0035002C0031003200340035000000200000005400610062006C00650056006900650077004D006F00640065003A00310000000100000008001E00000032002C0030002C003200380034002C0030002C0032003700390030000000200000005400610062006C00650056006900650077004D006F00640065003A00320000000100000008001E00000032002C0030002C003200380034002C0030002C0032003200390035000000200000005400610062006C00650056006900650077004D006F00640065003A00330000000100000008001E00000032002C0030002C003200380034002C0030002C0032003200390035000000200000005400610062006C00650056006900650077004D006F00640065003A00340000000100000008003E00000034002C0030002C003200380034002C0030002C0032003200390035002C00310032002C0032003700310035002C00310031002C00310036003600350000000500000005000000000000004E0000000100350001000000640062006F00000046004B005F005F0074005F0064006F0072006D005F005F00690064005F00610070006100720074005F005F003500320035003900330043004200380000000000000000000000C402000000000600000006000000050000000800000001761A1380761A130000000000000000AD0F0000010000070000000700000000000000000000000000000000000000D00200000600280000004100630074006900760065005400610062006C00650056006900650077004D006F006400650000000100000008000400000031000000200000005400610062006C00650056006900650077004D006F00640065003A00300000000100000008003A00000034002C0030002C003200380034002C0030002C0032003200390035002C0031002C0031003800370035002C0035002C0031003200340035000000200000005400610062006C00650056006900650077004D006F00640065003A00310000000100000008001E00000032002C0030002C003200380034002C0030002C0032003700390030000000200000005400610062006C00650056006900650077004D006F00640065003A00320000000100000008001E00000032002C0030002C003200380034002C0030002C0032003200390035000000200000005400610062006C00650056006900650077004D006F00640065003A00330000000100000008001E00000032002C0030002C003200380034002C0030002C0032003200390035000000200000005400610062006C00650056006900650077004D006F00640065003A00340000000100000008003E00000034002C0030002C003200380034002C0030002C0032003200390035002C00310032002C0032003700310035002C00310031002C0031003600360035000000080000000800000000000000200000000100000001000000640062006F000000690064005F0064006F0072006D0000000000000000000000C4020000000009000000090000000800000008000000017D1A13807D1A130000000000000000AD0F00000100000A0000000A00000000000000000000000000000000000000D00200000600280000004100630074006900760065005400610062006C00650056006900650077004D006F006400650000000100000008000400000031000000200000005400610062006C00650056006900650077004D006F00640065003A00300000000100000008003A00000034002C0030002C003200380034002C0030002C0032003200390035002C0031002C0031003800370035002C0035002C0031003200340035000000200000005400610062006C00650056006900650077004D006F00640065003A00310000000100000008001E00000032002C0030002C003200380034002C0030002C0032003700390030000000200000005400610062006C00650056006900650077004D006F00640065003A00320000000100000008001E00000032002C0030002C003200380034002C0030002C0032003200390035000000200000005400610062006C00650056006900650077004D006F00640065003A00330000000100000008001E00000032002C0030002C003200380034002C0030002C0032003200390035000000200000005400610062006C00650056006900650077004D006F00640065003A00340000000100000008003E00000034002C0030002C003200380034002C0030002C0032003200390035002C00310032002C0032003700310035002C00310031002C00310036003600350000000A000000050000000300000004000000680000006500000008000000040000000700000027000000260000000000000000000000000000000000010003000000000000000C0000000B0000004E61BC00000000000000000000000000000000000000000000000000000000000000000000000000000000000000DBE6B0E91C81D011AD5100A0C90F57390000020020B49438A81CD401020200001048450000000000000000000000000000000000820100004400610074006100200053006F0075007200630065003D004400450053004B0054004F0050002D0042004500420053004A00430054003B0049006E0069007400690061006C00200043006100740061006C006F0067003D006A006400620063003B005000650072007300690073007400200053006500630075007200690074007900200049006E0066006F003D0054007200750065003B0055007300650072002000490044003D00730061003B004D0075006C007400690070006C00650041006300740069007600650052006500730075006C00740053006500740073003D00460061006C00730065003B005000610063006B00650074002000530069007A0065003D0034003000390036003B004100700070006C00690063006100740069006F006E0020004E0061006D0065003D0022004D006900630072006F0073006F00660074002000530051004C00200053006500720076006500720020004D0061006E006100670065006D0065006E0074002000530074007500640069006F002200000000800500140000004400690061006700720061006D005F00300000000002260008000000620075007300000008000000640062006F0000000002260008000000630061007200000008000000640062006F000000000226001000000074005F0061007000610072007400000008000000640062006F000000000226000E00000074005F0064006F0072006D00000008000000640062006F000000000226000C00000074005F00730074007500000008000000640062006F00000000022400040000007500000008000000640062006F00000001000000D68509B3BB6BF2459AB8371664F0327008004E0000007B00310036003300340043004400440037002D0030003800380038002D0034003200450033002D0039004600410032002D004200360044003300320035003600330042003900310044007D000000000000000000000000000000010003000000000000000C0000000B000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000062885214)
GO

SET IDENTITY_INSERT [dbo].[sysdiagrams] OFF
GO


-- ----------------------------
-- Table structure for t_admin
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[t_admin]') AND type IN ('U'))
	DROP TABLE [dbo].[t_admin]
GO

CREATE TABLE [dbo].[t_admin] (
  [id] int  NOT NULL,
  [name] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [password] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL
)
GO

ALTER TABLE [dbo].[t_admin] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Records of t_admin
-- ----------------------------
INSERT INTO [dbo].[t_admin]  VALUES (N'1', N'admin', N'0000')
GO


-- ----------------------------
-- Table structure for t_apair
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[t_apair]') AND type IN ('U'))
	DROP TABLE [dbo].[t_apair]
GO

CREATE TABLE [dbo].[t_apair] (
  [id_dorm] int  NULL,
  [id_apart] int  NULL,
  [des] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [isOK] int  NULL,
  [id] int  IDENTITY(1,1) NOT NULL
)
GO

ALTER TABLE [dbo].[t_apair] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Records of t_apair
-- ----------------------------
SET IDENTITY_INSERT [dbo].[t_apair] ON
GO

INSERT INTO [dbo].[t_apair] ([id_dorm], [id_apart], [des], [isOK], [id]) VALUES (N'1', N'2', N'断电', N'1', N'1')
GO

INSERT INTO [dbo].[t_apair] ([id_dorm], [id_apart], [des], [isOK], [id]) VALUES (N'1', N'2', N'断水', N'0', N'2')
GO

SET IDENTITY_INSERT [dbo].[t_apair] OFF
GO


-- ----------------------------
-- Table structure for t_apart
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[t_apart]') AND type IN ('U'))
	DROP TABLE [dbo].[t_apart]
GO

CREATE TABLE [dbo].[t_apart] (
  [id] int  NOT NULL,
  [num_dorm] int  NULL,
  [cap_stu] int  NULL,
  [num_stu] int  NULL,
  [sex] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL
)
GO

ALTER TABLE [dbo].[t_apart] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Records of t_apart
-- ----------------------------
INSERT INTO [dbo].[t_apart]  VALUES (N'1', N'2', N'8', N'2', N'女')
GO

INSERT INTO [dbo].[t_apart]  VALUES (N'2', N'2', N'8', N'3', N'男')
GO


-- ----------------------------
-- Table structure for t_dorm
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[t_dorm]') AND type IN ('U'))
	DROP TABLE [dbo].[t_dorm]
GO

CREATE TABLE [dbo].[t_dorm] (
  [id] int  NOT NULL,
  [id_apart] int  NOT NULL,
  [capacity] int  NULL,
  [size] int  NULL
)
GO

ALTER TABLE [dbo].[t_dorm] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Records of t_dorm
-- ----------------------------
INSERT INTO [dbo].[t_dorm]  VALUES (N'1', N'1', N'4', N'1')
GO

INSERT INTO [dbo].[t_dorm]  VALUES (N'1', N'2', N'4', N'2')
GO

INSERT INTO [dbo].[t_dorm]  VALUES (N'2', N'1', N'4', N'1')
GO

INSERT INTO [dbo].[t_dorm]  VALUES (N'2', N'2', N'4', N'1')
GO


-- ----------------------------
-- Table structure for t_health
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[t_health]') AND type IN ('U'))
	DROP TABLE [dbo].[t_health]
GO

CREATE TABLE [dbo].[t_health] (
  [month] int  NOT NULL,
  [id_dorm] int  NOT NULL,
  [id_apart] int  NOT NULL,
  [score] int  NULL,
  [id] int  IDENTITY(1,1) NOT NULL
)
GO

ALTER TABLE [dbo].[t_health] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Records of t_health
-- ----------------------------
SET IDENTITY_INSERT [dbo].[t_health] ON
GO

INSERT INTO [dbo].[t_health] ([month], [id_dorm], [id_apart], [score], [id]) VALUES (N'7', N'1', N'1', N'9', N'1')
GO

INSERT INTO [dbo].[t_health] ([month], [id_dorm], [id_apart], [score], [id]) VALUES (N'7', N'1', N'2', N'7', N'2')
GO

SET IDENTITY_INSERT [dbo].[t_health] OFF
GO


-- ----------------------------
-- Table structure for t_stu
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[t_stu]') AND type IN ('U'))
	DROP TABLE [dbo].[t_stu]
GO

CREATE TABLE [dbo].[t_stu] (
  [id] int  NOT NULL,
  [name] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [sex] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [id_bed] int  NULL,
  [id_dorm] int  NULL,
  [id_apart] int  NULL,
  [password] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [isHere] int  NULL
)
GO

ALTER TABLE [dbo].[t_stu] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Records of t_stu
-- ----------------------------
INSERT INTO [dbo].[t_stu]  VALUES (N'1', N'小张', N'女', N'1', N'1', N'1', N'0000', N'0')
GO

INSERT INTO [dbo].[t_stu]  VALUES (N'2', N'张三', N'男', N'1', N'1', N'2', N'0000', N'0')
GO

INSERT INTO [dbo].[t_stu]  VALUES (N'3', N'李四', N'男', N'2', N'1', N'2', N'0000', N'0')
GO

INSERT INTO [dbo].[t_stu]  VALUES (N'4', N'小丽', N'女', N'1', N'2', N'1', N'0000', N'0')
GO

INSERT INTO [dbo].[t_stu]  VALUES (N'6', N'王五', N'男', N'3', N'2', N'2', N'0000', N'0')
GO


-- ----------------------------
-- Table structure for t_visitor
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[t_visitor]') AND type IN ('U'))
	DROP TABLE [dbo].[t_visitor]
GO

CREATE TABLE [dbo].[t_visitor] (
  [id] int  NOT NULL,
  [id_stu] int  NOT NULL,
  [start_time] varchar(255) COLLATE Chinese_PRC_CI_AS  NOT NULL,
  [dur_time] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [des] varchar(255) COLLATE Chinese_PRC_CI_AS  NOT NULL
)
GO

ALTER TABLE [dbo].[t_visitor] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Records of t_visitor
-- ----------------------------
INSERT INTO [dbo].[t_visitor]  VALUES (N'1', N'1', N'2018-07-18', N'1h', N'')
GO

INSERT INTO [dbo].[t_visitor]  VALUES (N'2', N'1', N'2018-07-18:07', N'2h', N'生日')
GO

INSERT INTO [dbo].[t_visitor]  VALUES (N'1234', N'2', N'2018-07-19:07', N'1h', N'看病')
GO


-- ----------------------------
-- Procedure structure for sp_upgraddiagrams
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_upgraddiagrams]') AND type IN ('P', 'PC', 'RF', 'X'))
	DROP PROCEDURE[dbo].[sp_upgraddiagrams]
GO

CREATE PROCEDURE [dbo].[sp_upgraddiagrams]
	AS
	BEGIN
		IF OBJECT_ID(N'dbo.sysdiagrams') IS NOT NULL
			return 0;
	
		CREATE TABLE dbo.sysdiagrams
		(
			name sysname NOT NULL,
			principal_id int NOT NULL,	-- we may change it to varbinary(85)
			diagram_id int PRIMARY KEY IDENTITY,
			version int,
	
			definition varbinary(max)
			CONSTRAINT UK_principal_name UNIQUE
			(
				principal_id,
				name
			)
		);


		/* Add this if we need to have some form of extended properties for diagrams */
		/*
		IF OBJECT_ID(N'dbo.sysdiagram_properties') IS NULL
		BEGIN
			CREATE TABLE dbo.sysdiagram_properties
			(
				diagram_id int,
				name sysname,
				value varbinary(max) NOT NULL
			)
		END
		*/

		IF OBJECT_ID(N'dbo.dtproperties') IS NOT NULL
		begin
			insert into dbo.sysdiagrams
			(
				[name],
				[principal_id],
				[version],
				[definition]
			)
			select	 
				convert(sysname, dgnm.[uvalue]),
				DATABASE_PRINCIPAL_ID(N'dbo'),			-- will change to the sid of sa
				0,							-- zero for old format, dgdef.[version],
				dgdef.[lvalue]
			from dbo.[dtproperties] dgnm
				inner join dbo.[dtproperties] dggd on dggd.[property] = 'DtgSchemaGUID' and dggd.[objectid] = dgnm.[objectid]	
				inner join dbo.[dtproperties] dgdef on dgdef.[property] = 'DtgSchemaDATA' and dgdef.[objectid] = dgnm.[objectid]
				
			where dgnm.[property] = 'DtgSchemaNAME' and dggd.[uvalue] like N'_EA3E6268-D998-11CE-9454-00AA00A3F36E_' 
			return 2;
		end
		return 1;
	END
GO


-- ----------------------------
-- Procedure structure for sp_helpdiagrams
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_helpdiagrams]') AND type IN ('P', 'PC', 'RF', 'X'))
	DROP PROCEDURE[dbo].[sp_helpdiagrams]
GO

CREATE PROCEDURE [dbo].[sp_helpdiagrams]
	(
		@diagramname sysname = NULL,
		@owner_id int = NULL
	)
	WITH EXECUTE AS N'dbo'
	AS
	BEGIN
		DECLARE @user sysname
		DECLARE @dboLogin bit
		EXECUTE AS CALLER;
			SET @user = USER_NAME();
			SET @dboLogin = CONVERT(bit,IS_MEMBER('db_owner'));
		REVERT;
		SELECT
			[Database] = DB_NAME(),
			[Name] = name,
			[ID] = diagram_id,
			[Owner] = USER_NAME(principal_id),
			[OwnerID] = principal_id
		FROM
			sysdiagrams
		WHERE
			(@dboLogin = 1 OR USER_NAME(principal_id) = @user) AND
			(@diagramname IS NULL OR name = @diagramname) AND
			(@owner_id IS NULL OR principal_id = @owner_id)
		ORDER BY
			4, 5, 1
	END
GO


-- ----------------------------
-- Procedure structure for sp_helpdiagramdefinition
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_helpdiagramdefinition]') AND type IN ('P', 'PC', 'RF', 'X'))
	DROP PROCEDURE[dbo].[sp_helpdiagramdefinition]
GO

CREATE PROCEDURE [dbo].[sp_helpdiagramdefinition]
	(
		@diagramname 	sysname,
		@owner_id	int	= null 		
	)
	WITH EXECUTE AS N'dbo'
	AS
	BEGIN
		set nocount on

		declare @theId 		int
		declare @IsDbo 		int
		declare @DiagId		int
		declare @UIDFound	int
	
		if(@diagramname is null)
		begin
			RAISERROR (N'E_INVALIDARG', 16, 1);
			return -1
		end
	
		execute as caller;
		select @theId = DATABASE_PRINCIPAL_ID();
		select @IsDbo = IS_MEMBER(N'db_owner');
		if(@owner_id is null)
			select @owner_id = @theId;
		revert; 
	
		select @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams where principal_id = @owner_id and name = @diagramname;
		if(@DiagId IS NULL or (@IsDbo = 0 and @UIDFound <> @theId ))
		begin
			RAISERROR ('Diagram does not exist or you do not have permission.', 16, 1);
			return -3
		end

		select version, definition FROM dbo.sysdiagrams where diagram_id = @DiagId ; 
		return 0
	END
GO


-- ----------------------------
-- Procedure structure for sp_creatediagram
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_creatediagram]') AND type IN ('P', 'PC', 'RF', 'X'))
	DROP PROCEDURE[dbo].[sp_creatediagram]
GO

CREATE PROCEDURE [dbo].[sp_creatediagram]
	(
		@diagramname 	sysname,
		@owner_id		int	= null, 	
		@version 		int,
		@definition 	varbinary(max)
	)
	WITH EXECUTE AS 'dbo'
	AS
	BEGIN
		set nocount on
	
		declare @theId int
		declare @retval int
		declare @IsDbo	int
		declare @userName sysname
		if(@version is null or @diagramname is null)
		begin
			RAISERROR (N'E_INVALIDARG', 16, 1);
			return -1
		end
	
		execute as caller;
		select @theId = DATABASE_PRINCIPAL_ID(); 
		select @IsDbo = IS_MEMBER(N'db_owner');
		revert; 
		
		if @owner_id is null
		begin
			select @owner_id = @theId;
		end
		else
		begin
			if @theId <> @owner_id
			begin
				if @IsDbo = 0
				begin
					RAISERROR (N'E_INVALIDARG', 16, 1);
					return -1
				end
				select @theId = @owner_id
			end
		end
		-- next 2 line only for test, will be removed after define name unique
		if EXISTS(select diagram_id from dbo.sysdiagrams where principal_id = @theId and name = @diagramname)
		begin
			RAISERROR ('The name is already used.', 16, 1);
			return -2
		end
	
		insert into dbo.sysdiagrams(name, principal_id , version, definition)
				VALUES(@diagramname, @theId, @version, @definition) ;
		
		select @retval = @@IDENTITY 
		return @retval
	END
GO


-- ----------------------------
-- Procedure structure for sp_renamediagram
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_renamediagram]') AND type IN ('P', 'PC', 'RF', 'X'))
	DROP PROCEDURE[dbo].[sp_renamediagram]
GO

CREATE PROCEDURE [dbo].[sp_renamediagram]
	(
		@diagramname 		sysname,
		@owner_id		int	= null,
		@new_diagramname	sysname
	
	)
	WITH EXECUTE AS 'dbo'
	AS
	BEGIN
		set nocount on
		declare @theId 			int
		declare @IsDbo 			int
		
		declare @UIDFound 		int
		declare @DiagId			int
		declare @DiagIdTarg		int
		declare @u_name			sysname
		if((@diagramname is null) or (@new_diagramname is null))
		begin
			RAISERROR ('Invalid value', 16, 1);
			return -1
		end
	
		EXECUTE AS CALLER;
		select @theId = DATABASE_PRINCIPAL_ID();
		select @IsDbo = IS_MEMBER(N'db_owner'); 
		if(@owner_id is null)
			select @owner_id = @theId;
		REVERT;
	
		select @u_name = USER_NAME(@owner_id)
	
		select @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams where principal_id = @owner_id and name = @diagramname 
		if(@DiagId IS NULL or (@IsDbo = 0 and @UIDFound <> @theId))
		begin
			RAISERROR ('Diagram does not exist or you do not have permission.', 16, 1)
			return -3
		end
	
		-- if((@u_name is not null) and (@new_diagramname = @diagramname))	-- nothing will change
		--	return 0;
	
		if(@u_name is null)
			select @DiagIdTarg = diagram_id from dbo.sysdiagrams where principal_id = @theId and name = @new_diagramname
		else
			select @DiagIdTarg = diagram_id from dbo.sysdiagrams where principal_id = @owner_id and name = @new_diagramname
	
		if((@DiagIdTarg is not null) and  @DiagId <> @DiagIdTarg)
		begin
			RAISERROR ('The name is already used.', 16, 1);
			return -2
		end		
	
		if(@u_name is null)
			update dbo.sysdiagrams set [name] = @new_diagramname, principal_id = @theId where diagram_id = @DiagId
		else
			update dbo.sysdiagrams set [name] = @new_diagramname where diagram_id = @DiagId
		return 0
	END
GO


-- ----------------------------
-- Procedure structure for sp_alterdiagram
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_alterdiagram]') AND type IN ('P', 'PC', 'RF', 'X'))
	DROP PROCEDURE[dbo].[sp_alterdiagram]
GO

CREATE PROCEDURE [dbo].[sp_alterdiagram]
	(
		@diagramname 	sysname,
		@owner_id	int	= null,
		@version 	int,
		@definition 	varbinary(max)
	)
	WITH EXECUTE AS 'dbo'
	AS
	BEGIN
		set nocount on
	
		declare @theId 			int
		declare @retval 		int
		declare @IsDbo 			int
		
		declare @UIDFound 		int
		declare @DiagId			int
		declare @ShouldChangeUID	int
	
		if(@diagramname is null)
		begin
			RAISERROR ('Invalid ARG', 16, 1)
			return -1
		end
	
		execute as caller;
		select @theId = DATABASE_PRINCIPAL_ID();	 
		select @IsDbo = IS_MEMBER(N'db_owner'); 
		if(@owner_id is null)
			select @owner_id = @theId;
		revert;
	
		select @ShouldChangeUID = 0
		select @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams where principal_id = @owner_id and name = @diagramname 
		
		if(@DiagId IS NULL or (@IsDbo = 0 and @theId <> @UIDFound))
		begin
			RAISERROR ('Diagram does not exist or you do not have permission.', 16, 1);
			return -3
		end
	
		if(@IsDbo <> 0)
		begin
			if(@UIDFound is null or USER_NAME(@UIDFound) is null) -- invalid principal_id
			begin
				select @ShouldChangeUID = 1 ;
			end
		end

		-- update dds data			
		update dbo.sysdiagrams set definition = @definition where diagram_id = @DiagId ;

		-- change owner
		if(@ShouldChangeUID = 1)
			update dbo.sysdiagrams set principal_id = @theId where diagram_id = @DiagId ;

		-- update dds version
		if(@version is not null)
			update dbo.sysdiagrams set version = @version where diagram_id = @DiagId ;

		return 0
	END
GO


-- ----------------------------
-- Procedure structure for sp_dropdiagram
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_dropdiagram]') AND type IN ('P', 'PC', 'RF', 'X'))
	DROP PROCEDURE[dbo].[sp_dropdiagram]
GO

CREATE PROCEDURE [dbo].[sp_dropdiagram]
	(
		@diagramname 	sysname,
		@owner_id	int	= null
	)
	WITH EXECUTE AS 'dbo'
	AS
	BEGIN
		set nocount on
		declare @theId 			int
		declare @IsDbo 			int
		
		declare @UIDFound 		int
		declare @DiagId			int
	
		if(@diagramname is null)
		begin
			RAISERROR ('Invalid value', 16, 1);
			return -1
		end
	
		EXECUTE AS CALLER;
		select @theId = DATABASE_PRINCIPAL_ID();
		select @IsDbo = IS_MEMBER(N'db_owner'); 
		if(@owner_id is null)
			select @owner_id = @theId;
		REVERT; 
		
		select @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams where principal_id = @owner_id and name = @diagramname 
		if(@DiagId IS NULL or (@IsDbo = 0 and @UIDFound <> @theId))
		begin
			RAISERROR ('Diagram does not exist or you do not have permission.', 16, 1)
			return -3
		end
	
		delete from dbo.sysdiagrams where diagram_id = @DiagId;
	
		return 0;
	END
GO


-- ----------------------------
-- Function structure for fn_diagramobjects
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_diagramobjects]') AND type IN ('FN', 'FS', 'FT', 'IF', 'TF'))
	DROP FUNCTION[dbo].[fn_diagramobjects]
GO

CREATE FUNCTION [dbo].[fn_diagramobjects]() 
	RETURNS int
	WITH EXECUTE AS N'dbo'
	AS
	BEGIN
		declare @id_upgraddiagrams		int
		declare @id_sysdiagrams			int
		declare @id_helpdiagrams		int
		declare @id_helpdiagramdefinition	int
		declare @id_creatediagram	int
		declare @id_renamediagram	int
		declare @id_alterdiagram 	int 
		declare @id_dropdiagram		int
		declare @InstalledObjects	int

		select @InstalledObjects = 0

		select 	@id_upgraddiagrams = object_id(N'dbo.sp_upgraddiagrams'),
			@id_sysdiagrams = object_id(N'dbo.sysdiagrams'),
			@id_helpdiagrams = object_id(N'dbo.sp_helpdiagrams'),
			@id_helpdiagramdefinition = object_id(N'dbo.sp_helpdiagramdefinition'),
			@id_creatediagram = object_id(N'dbo.sp_creatediagram'),
			@id_renamediagram = object_id(N'dbo.sp_renamediagram'),
			@id_alterdiagram = object_id(N'dbo.sp_alterdiagram'), 
			@id_dropdiagram = object_id(N'dbo.sp_dropdiagram')

		if @id_upgraddiagrams is not null
			select @InstalledObjects = @InstalledObjects + 1
		if @id_sysdiagrams is not null
			select @InstalledObjects = @InstalledObjects + 2
		if @id_helpdiagrams is not null
			select @InstalledObjects = @InstalledObjects + 4
		if @id_helpdiagramdefinition is not null
			select @InstalledObjects = @InstalledObjects + 8
		if @id_creatediagram is not null
			select @InstalledObjects = @InstalledObjects + 16
		if @id_renamediagram is not null
			select @InstalledObjects = @InstalledObjects + 32
		if @id_alterdiagram  is not null
			select @InstalledObjects = @InstalledObjects + 64
		if @id_dropdiagram is not null
			select @InstalledObjects = @InstalledObjects + 128
		
		return @InstalledObjects 
	END
GO


-- ----------------------------
-- Uniques structure for table sysdiagrams
-- ----------------------------
ALTER TABLE [dbo].[sysdiagrams] ADD CONSTRAINT [UK_principal_name] UNIQUE NONCLUSTERED ([principal_id] ASC, [name] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Primary Key structure for table sysdiagrams
-- ----------------------------
ALTER TABLE [dbo].[sysdiagrams] ADD CONSTRAINT [PK__sysdiagr__C2B05B619CD80CF9] PRIMARY KEY CLUSTERED ([diagram_id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Primary Key structure for table t_admin
-- ----------------------------
ALTER TABLE [dbo].[t_admin] ADD CONSTRAINT [PK__t_admin__3213E83F73B8155C] PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Primary Key structure for table t_apair
-- ----------------------------
ALTER TABLE [dbo].[t_apair] ADD CONSTRAINT [PK__t_apair__3213E83FDCA8DF17] PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Primary Key structure for table t_apart
-- ----------------------------
ALTER TABLE [dbo].[t_apart] ADD CONSTRAINT [PK__t_apart__3213E83F46C46898] PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Primary Key structure for table t_dorm
-- ----------------------------
ALTER TABLE [dbo].[t_dorm] ADD CONSTRAINT [PK__t_dorm__3213E83FC28A46E7] PRIMARY KEY CLUSTERED ([id], [id_apart])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Primary Key structure for table t_health
-- ----------------------------
ALTER TABLE [dbo].[t_health] ADD CONSTRAINT [PK__t_health__3213E83F462CA32A] PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Primary Key structure for table t_stu
-- ----------------------------
ALTER TABLE [dbo].[t_stu] ADD CONSTRAINT [PK__t_stu__3213E83FE5CD664D] PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Primary Key structure for table t_visitor
-- ----------------------------
ALTER TABLE [dbo].[t_visitor] ADD CONSTRAINT [PK__t_visito__3213E83FD7CAA34A] PRIMARY KEY CLUSTERED ([id_stu], [start_time], [id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Foreign Keys structure for table t_apair
-- ----------------------------
ALTER TABLE [dbo].[t_apair] ADD CONSTRAINT [FK__t_apair__72C60C4A] FOREIGN KEY ([id_dorm], [id_apart]) REFERENCES [dbo].[t_dorm] ([id], [id_apart]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO


-- ----------------------------
-- Foreign Keys structure for table t_dorm
-- ----------------------------
ALTER TABLE [dbo].[t_dorm] ADD CONSTRAINT [FK__t_dorm__id_apart__52593CB8] FOREIGN KEY ([id_apart]) REFERENCES [dbo].[t_apart] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO


-- ----------------------------
-- Foreign Keys structure for table t_health
-- ----------------------------
ALTER TABLE [dbo].[t_health] ADD CONSTRAINT [FK__t_health__6FE99F9F] FOREIGN KEY ([id_dorm], [id_apart]) REFERENCES [dbo].[t_dorm] ([id], [id_apart]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO


-- ----------------------------
-- Foreign Keys structure for table t_stu
-- ----------------------------
ALTER TABLE [dbo].[t_stu] ADD CONSTRAINT [id_dorm] FOREIGN KEY ([id_dorm], [id_apart]) REFERENCES [dbo].[t_dorm] ([id], [id_apart]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO


-- ----------------------------
-- Foreign Keys structure for table t_visitor
-- ----------------------------
ALTER TABLE [dbo].[t_visitor] ADD CONSTRAINT [FK__t_visitor__id_st__75A278F5] FOREIGN KEY ([id_stu]) REFERENCES [dbo].[t_stu] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

